"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : 兼容M* create function
Case Name   : create function 严格与非严格模式下调用错误
Create At   : 2023.03.15
@zou_jialiang0501129488
Description :
    1、创建兼容b库并查询插件是否加载成功
    2、create function 使用兼容性语句创建函数
    3、在严格模式下调用函数不存在行
    4、在非严格模式下调用函数不存在行
    5、清理环境
Expect      :
    1、成功，插件查询成功
    2、成功
    3、成功、返回null
    4、成功，返回null
    5、清理成功
History     :
"""

import os
import unittest

from yat.test import macro

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class CompatibilityTest01(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.common = Common()
        self.sh_primary = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.db_name = 'db_create_function_0043'
        self.sql_name = 'sql_file_0043.sql'

    def test_create_function(self):
        text = '-----step1:创建兼容b库库并查询插件;expect:成功-----'
        self.log.info(text)
        sql_cmd = f"drop database if exists {self.db_name};" \
                  f"create database {self.db_name} dbcompatibility ='B';"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS,
                      sql_res, '兼容B库创建失败')

        sql_cmd1 = "select extname from pg_extension where extname ='dolphin';"
        self.log.info(sql_cmd1)
        sql_res1 = self.sh_primary.execut_db_sql(sql_cmd1, dbname=self.db_name)
        self.log.info(sql_res1)
        self.assertIn('dolphin', sql_res1, '查询失败')

        text = '-----step2:create function 使用兼容性语句创建包含selelct into语句函数;' \
               'expect:成功-----'
        self.log.info(text)
        sql_cmd2 = f"""drop table if exists t_create_function_0043;
                    drop table if exists t_create_function_0043a;
                    create table t_create_function_0043(id int,name text);
                    insert into t_create_function_0043 values(1,'a'),(2,'b');
                    create table t_create_function_0043a(id int);
                    drop function if exists f_create_function_0043;
                    delimiter |
                    create function f_create_function_0043(par varchar(16))
                    returns int reads sql data
                    begin
                    declare t_create_function_0043a int;
                    select id into t_create_function_0043a from 
                    t_create_function_0043 where name = par;
                    return t_create_function_0043a;
                    end |
                    delimiter ;
        """
        self.log.info(sql_cmd2)
        echo_cmd = f"rm -rf {self.sql_name};" \
                   f"echo \"\"\"{sql_cmd2}\"\"\" > {self.sql_name};" \
                   f"ls | grep {self.sql_name}"
        self.log.info(echo_cmd)
        res = self.common.get_sh_result(self.sh_primary.node, echo_cmd)
        self.assertIn(self.sql_name, res, '文件写入失败')

        sql_cmd3 = f'source {macro.DB_ENV_PATH};gsql -d {self.db_name} ' \
                   f'-p {self.sh_primary.node.db_port} ' \
                   f'-f {self.sql_name}'
        self.log.info(sql_cmd3)
        sql_res3 = self.common.get_sh_result(self.sh_primary.node, sql_cmd3)
        self.log.info(sql_res3)
        self.assertIn('CREATE FUNCTION', sql_res3, '函数调用失败')

        text = '-----step2:严格模式下调用函数指定不存在行; expect:失败-----'
        self.log.info(text)
        sql_cmd = "set dolphin.sql_mode = sql_mode_strict;" \
                  "select f_create_function_0043('aaa');"
        self.log.info(sql_cmd)
        res = self.sh_primary.execut_db_sql(sql_cmd, dbname=self.db_name)
        self.log.info(res)
        msg = 'f_create_function_0043 ' \
              '\n------------------------\n' \
              '                       \n(1 row)'
        self.assertIn('SET', res, 'sql_mode设置失败')
        self.assertIn(msg, res, 'sql_mode设置失败')

        text = '-----step2:非严格模式下调用函数指定不存在行; expect:失败-----'
        self.log.info(text)
        sql_cmd = "set dolphin.sql_mode = sql_mode_full_group;" \
                  "select f_create_function_0043('aaa');"
        self.log.info(sql_cmd)
        res = self.sh_primary.execut_db_sql(sql_cmd, dbname=self.db_name)
        self.log.info(res)
        self.assertIn('SET', res, 'sql_mode设置失败')
        self.assertIn(msg, res, 'sql_mode设置失败')

    def tearDown(self):
        text = '-----step3:清理环境;expect:成功-----'
        self.log.info(text)
        sql_cmd4 = f"drop database if exists {self.db_name};"
        self.log.info(sql_cmd4)
        sql_res4 = self.sh_primary.execut_db_sql(sql_cmd4)
        self.log.info(sql_res4)
        del_cmd = f'rm -rf {self.sql_name};' \
                  f'if [ -e {self.sql_name} ]; ' \
                  f'then echo "exists"; else echo "not exists"; fi'
        self.log.info(del_cmd)
        del_res = self.common.get_sh_result(self.sh_primary.node, del_cmd)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS,
                      sql_res4, '清理环境失败')
        self.assertIn('not exists', del_res, '文件删除失败')
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
